/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import DTO.StudentDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Arturia
 */
public class StudentDAO {
public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(student_id) = '1' THEN CAST(student_id AS INT) "
                    + "ELSE NULL END FROM t_student) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public static ArrayList LoadDatabase(Connection con, StudentDTO student) {
        ArrayList<StudentDTO> result = new ArrayList<StudentDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (student == null) {
                sql = "SELECT * FROM t_student";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                StudentDTO studentTemp = new StudentDTO();
                studentTemp = new StudentDTO();

                studentTemp.setStrStudentID(rs.getString(1));

                if (rs.wasNull()) {
                    studentTemp.setStrStudentID(generateID(con));
                }

                studentTemp.setStrPersonID(rs.getString(2));

                if (rs.wasNull()) {
                    studentTemp.setStrPersonID("");
                }

                studentTemp.setStrFatherID(rs.getString(3));

                if (rs.wasNull()) {
                    studentTemp.setStrFatherID("");
                }

                studentTemp.setStrMotherID(rs.getString(4));

                if (rs.wasNull()) {
                    studentTemp.setStrMotherID("");
                }

                studentTemp.setStrStudentID(rs.getString(5));

                if (rs.wasNull()) {
                    studentTemp.setStrClassID("");
                }
                result.add(studentTemp);
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, StudentDTO student) {
        int result = 0;
        try {
            String sql = "INSERT INTO t_student VALUES(?,?,?,?,?,?,?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, student.getStrStudentID());
            stm.setString(2, student.getStrPersonID());
            stm.setString(3, student.getStrFatherID());
            stm.setString(4, student.getStrMotherID());
            stm.setString(5, student.getStrClassID());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, StudentDTO student) {
        int result = 0;
        try {
            String query = "UPDATE t_student SET person_id=?, father_id=?, mother_id=?, "
                    + "class_id=? "
                    + "WHERE student_id=?";
            PreparedStatement stm = con.prepareStatement(query);
            
            stm.setString(1, student.getStrPersonID());
            stm.setString(2, student.getStrFatherID());
            stm.setString(3, student.getStrMotherID());
            stm.setString(4, student.getStrClassID());
            stm.setString(5, student.getStrStudentID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String studentID) {
        int result = 0;
        try {
            String sql = "DELETE FROM t_student WHERE student_id=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, studentID);
            result = stm.executeUpdate();
            stm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
